﻿// Less2DB.NET framework
// http://code.google.com/p/less2db/
//
// Copyright © Wangzhongwu, 2011
// alex.wzw@gmail.com
//

namespace Less2DB
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Reflection.Emit;
    using System.Linq.Expressions;

    /// <summary>
    /// 数据库查询器，提供实体的 CURD 操作。
    /// </summary>
    public class DBQuery
    {
        Dictionary<Less2DB.Interfaces.IDAL, IDbConnection> _dictConn = new Dictionary<Less2DB.Interfaces.IDAL, IDbConnection>();
        Dictionary<Less2DB.Interfaces.IDAL, IDbTransaction> _dictTran = new Dictionary<Less2DB.Interfaces.IDAL, IDbTransaction>();

        /// <summary>
        /// 执行 SQL 字符串语句。
        /// </summary>
        /// <param name="entity">实现 Less2DB.Interface.IEntity 接口的实例对象。</param>
        /// <param name="commandText">SQL 字符串语句。</param>
        /// <returns>返回 SQL 字符串语句执行后所影响的行数。</returns>
        public int ExecuteNonQuery(Less2DB.Interfaces.IEntity entity, string commandText)
        {
            this.BeginTransaction(entity);

            using (var command = this.CreateCommand(entity, commandText))
            {
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 将一个实体对象添加到数据库。
        /// </summary>
        /// <typeparam name="T">实现 Less2DB.Interface.IEntity 接口的派生类型。</typeparam>
        /// <param name="entity">实现 Less2DB.Interface.IEntity 接口的实例对象。</param>
        /// <returns>返回本操作执行后所影响的行数。</returns>
        public int Add<T>(T entity, params Expression<Func<T, object>>[] fieldsExpression) where T : Less2DB.Interfaces.IEntity
        {
            this.BeginTransaction(entity);

            Less2DB.SQLBuilder.SQLInsertBuilder builder;

            #region 设置准备更新的字段列表
            if (fieldsExpression.Length > 0)
            {
                var fields = DBQuery.ExpressionToFieldNames(fieldsExpression);

                builder = new SQLBuilder.SQLInsertBuilder(entity.TableName, fields);
            }
            else
            {
                builder = new SQLBuilder.SQLInsertBuilder(entity.TableName, entity.FieldNamesForCU);
            }
            #endregion

            var sqlText = builder.ToString();
            var sqlParameters = entity.GetParameters(builder.Fields);

            using (var command = this.CreateCommand(entity, sqlText, sqlParameters))
            {
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 更新一个实体到数据库。
        /// </summary>
        /// <typeparam name="T">实现 Less2DB.Interface.IEntity 接口的派生类型。</typeparam>
        /// <param name="entity">实现 Less2DB.Interface.IEntity 接口的实例对象。</param>
        /// <param name="conditionExpression">实现更新条件的 Lambda 表达式。</param>
        /// <param name="fieldsExpression">实现更新所需字段的 Lambda 表达式。</param>
        /// <returns>返回本操作执行后所影响的行数。</returns>
        public int Update<T>(T entity, Expression<Func<T, bool>> conditionExpression, params Expression<Func<T, object>>[] fieldsExpression) where T : Less2DB.Interfaces.IEntity
        {
            this.BeginTransaction(entity);

            Less2DB.SQLBuilder.SQLUpdateBuilder builder;

            #region 设置准备更新的字段列表
            if (fieldsExpression.Length > 0)
            {
                var fields = DBQuery.ExpressionToFieldNames(fieldsExpression);

                builder = new SQLBuilder.SQLUpdateBuilder(entity.TableName, fields);
            }
            else
            {
                builder = new SQLBuilder.SQLUpdateBuilder(entity.TableName, entity.FieldNamesForCU);
            } 
            #endregion

            #region 设置准备更新的查询条件
            if (conditionExpression != null)
            {
                builder.Where = DBQuery.ExpressionToWhere(conditionExpression);
            } 
            #endregion

            var sqlText = builder.ToString();
            var sqlParameters = entity.GetParameters(builder.Fields);

            using (var command = this.CreateCommand(entity, sqlText, sqlParameters))
            {
                return command.ExecuteNonQuery();
            }
        }

        public void Fill<T>(T entity, Expression<Func<T, bool>> conditionExpression) where T : Less2DB.Interfaces.IEntity
        {
            this.BeginTransaction(entity);

            Less2DB.SQLBuilder.SQLSelectBuilder builder;

            builder = new SQLBuilder.SQLSelectBuilder(entity.TableName);
            builder.Where = DBQuery.ExpressionToWhere(conditionExpression);

            var sqlText = builder.ToString();
            var sqlParameters = entity.GetParameters(builder.Fields);

            using (var command = this.CreateCommand(entity, sqlText, sqlParameters))
            {
                DataTable table = new DataTable();

                var adapter = entity.DAL.CreateDataAdapter();

                adapter.Fill(table);
            }
        }

        /// <summary>
        /// 提交本查询器当前挂起的所有事务。
        /// </summary>
        public void Commit()
        {
            foreach (var tran in _dictTran.Values)
            {
                tran.Commit();
            }
        }

        /// <summary>
        /// 回滚本查询器当前挂起的所有事务。
        /// </summary>
        public void Rollback()
        {
            foreach (var tran in _dictTran.Values)
            {
                tran.Rollback();
            }
        }

        /// <summary>
        /// 关闭查询器打开的数据库连接。
        /// </summary>
        public void Close()
        {
            foreach (var conn in _dictConn.Values)
            {
                conn.Close();
            }
        }

        #region CreateCommand<T> 创建命令对象程序

        /// <summary>
        /// 使用基于 Less2DB.Interface.IEntity 接口的实例对象的数据库连接，创建并返回 IDbCommand 实例。
        /// </summary>
        /// <typeparam name="T">实现 Less2DB.Interface.IEntity 接口的派生类型。</typeparam>
        /// <param name="entity">实现 Less2DB.Interface.IEntity 接口的实例对象。</param>
        /// <returns>返回创建的 IDbCommand 实例对象。</returns>
        private IDbCommand CreateCommand(Less2DB.Interfaces.IEntity entity)
        {
            return CreateCommand(entity, string.Empty);
        }

        /// <summary>
        /// 使用基于 Less2DB.Interface.IEntity 接口的实例对象的数据库连接，创建并返回 IDbCommand 实例。
        /// </summary>
        /// <typeparam name="T">实现 Less2DB.Interface.IEntity 接口的派生类型。</typeparam>
        /// <param name="entity">实现 Less2DB.Interface.IEntity 接口的实例对象。</param>
        /// <param name="commandText">SQL 字符串语句。</param>
        /// <param name="paramArray">参数数组集合。</param>
        /// <returns>返回创建的 IDbCommand 实例对象。</returns>
        private IDbCommand CreateCommand(Less2DB.Interfaces.IEntity entity, string commandText, params IDbDataParameter[] paramArray)
        {
            IDbCommand command;

            command = _dictConn[entity.DAL].CreateCommand();
            command.Transaction = _dictTran[entity.DAL];
            command.CommandText = commandText;
            command.Parameters.AddRange(paramArray);

            return command;
        }

        #endregion

        /// <summary>
        /// 为实体启用事务化功能。
        /// </summary>
        /// <param name="entity">实现 Less2DB.Interface.IEntity 接口的实例对象。</param>
        private void BeginTransaction(Less2DB.Interfaces.IEntity entity)
        {
            if (!_dictConn.ContainsKey(entity.DAL))
            {
                lock (_dictConn)
                {
                    if (!_dictConn.ContainsKey(entity.DAL))
                    {
                        _dictConn.Add(entity.DAL, entity.DAL.CreateConnection(entity.ConnectionString));
                        _dictConn[entity.DAL].Open();
                    }
                }
            }

            if (!_dictTran.ContainsKey(entity.DAL))
            {
                lock (_dictTran)
                {
                    if (!_dictTran.ContainsKey(entity.DAL))
                    {
                        _dictTran.Add(entity.DAL, _dictConn[entity.DAL].BeginTransaction());
                    }
                }
            }
        }

        /// <summary>
        /// 将 Lambda 查询表达式转换为查询的字段字符串列表。
        /// </summary>
        /// <typeparam name="T">实现 Less2DB.Interface.IEntity 接口的派生类型。</typeparam>
        /// <param name="arguments">Lambda 查询表达式集合。</param>
        /// <returns>返回字段字符串列表。</returns>
        private static string[] ExpressionToFieldNames<T>(Expression<Func<T, object>>[] arguments) where T : Less2DB.Interfaces.IEntity
        {
            var fieldNames = new List<string>();

            foreach (var exp in arguments)
            {
                var dotPrefix = exp.Parameters[0].Name;
                var expBuilder = new StringBuilder(exp.Body.ToString());

                expBuilder.Replace(dotPrefix + ".", string.Empty);
                expBuilder.Replace("Convert(", string.Empty);
                expBuilder.Replace(")", string.Empty);

                fieldNames.Add(expBuilder.ToString());
            }

            return fieldNames.ToArray();
        }

        /// <summary>
        /// 将 Lambda 查询表达式转换为 SQL WHERE 的字符串查询条件。
        /// </summary>
        /// <typeparam name="T">实现 Less2DB.Interface.IEntity 接口的派生类型。</typeparam>
        /// <param name="exp">一个 Lambda 查询表达式。</param>
        /// <returns>返回转换成字符串的查询条件。</returns>
        private static string ExpressionToWhere<T>(Expression<Func<T, bool>> exp) where T : Less2DB.Interfaces.IEntity
        {
            var dotPrefix = exp.Parameters[0].Name;
            var expBuilder = new StringBuilder(exp.Body.ToString());

            expBuilder.Replace(dotPrefix + ".", string.Empty);
            expBuilder.Replace("AndAlso", "AND");
            expBuilder.Replace("OrElse", "OR");
            expBuilder.Replace("==", "=");
            expBuilder.Replace("!=", "<>");

            return "WHERE " + expBuilder.ToString();
        }
    }
}
